package dao;

import vo.Attendance;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class AttendanceDao {
    private Connection conn = null;

    private void initConnection() throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentManagerDB?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true","StuManager", "123456");
    }

    private void closeConnection() throws Exception{
        conn.close();
    }


    public List<Attendance> findWithStuId(String stu_id) throws Exception{
        initConnection();
        String sql = "select * from attendance where stu_id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, stu_id);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Attendance> attendances = new ArrayList<>();
        getAllAttendance(attendances, rs);
        rs.close();
        pstmt.close();
        return attendances;
    }

    private void getAllAttendance(ArrayList<Attendance> al, ResultSet rs) throws Exception{
        while (rs.next()){
            Attendance attendance = new Attendance();
            attendance.setStu_id(rs.getString("stu_id"));
            attendance.setCourse_id(rs.getString("course_id"));
            attendance.setAttendance_time(Timestamp.valueOf(rs.getTimestamp("attendance_time").toLocalDateTime()));
            attendance.setStatus(Attendance.attendencestatus.valueOf(rs.getString("status")));
            al.add(attendance);
        }
    }

    
    
    public Map<String, String> getCoursesByTeacher(String teacherId) throws Exception {
        initConnection();
        String sql = "SELECT course_id, attendance_time FROM attendance WHERE teacher_id = ? ORDER BY attendance_time DESC ";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, teacherId);

        ResultSet rs = pstmt.executeQuery();

        LinkedHashMap<String, String> courses = new LinkedHashMap<>();
        while (rs.next()) {
            String courseId = rs.getString("course_id");
            String attendanceTime = rs.getString("attendance_time");
            if (!courses.containsKey(courseId) || !courses.get(courseId).equals(attendanceTime)) {
                courses.put(attendanceTime, courseId);
            }
        }
        rs.close();
        pstmt.close();
        closeConnection();
        return courses;
    }

    public int getSumByCourseIdAndTime(String courseId, String time) throws Exception{
        initConnection();
        String sql = "select count(*) from attendance where course_id = ? and attendance_time = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, courseId);
        pstmt.setString(2, time);
        ResultSet rs = pstmt.executeQuery();
        int sum = 0;
        if (rs.next()){
            sum = rs.getInt(1);
        }
        rs.close();
        pstmt.close();
        closeConnection();
        return sum;
    }

    public int getAbsenceByCourseIdAndTime(String courseId, String time) throws Exception{
        initConnection();
        String sql = "select count(*) from attendance where course_id = ? and attendance_time = ? and status = '缺勤'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, courseId);
        pstmt.setString(2, time);
        ResultSet rs = pstmt.executeQuery();
        int sum = 0;
        if (rs.next()){
            sum = rs.getInt(1);
        }
        rs.close();
        pstmt.close();
        closeConnection();
        return sum;
    }

public int getLeaveByCourseIdAndTime(String courseId, String time) throws Exception{
        initConnection();
        String sql = "select count(*) from attendance where course_id = ? and attendance_time = ? and status = '请假'";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, courseId);
        pstmt.setString(2, time);
        ResultSet rs = pstmt.executeQuery();
        int sum = 0;
        if (rs.next()){
            sum = rs.getInt(1);
        }
        rs.close();
        pstmt.close();
        closeConnection();
        return sum;
    }
    
    
    public List<Attendance> getAttendanceByTimeAndTeacherId(String attendanceTime, String teacherId) throws Exception{
        initConnection();
        String sql = "select * from attendance where attendance_time = ? and course_id in (select id from course where teacher_id = ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, attendanceTime);
        pstmt.setString(2, teacherId);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Attendance> attendances = new ArrayList<>();
        getAllAttendance(attendances, rs);
        rs.close();
        pstmt.close();
        closeConnection();
        return attendances;
    }

    public void updateAttendance(String stuId, String attendanceTime, String teacherId, String status)throws Exception {
        initConnection();
        String sql = "update attendance set status = ? where stu_id = ? and attendance_time = ? and course_id in (select id from course where teacher_id = ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, status);
        pstmt.setString(2, stuId);
        pstmt.setString(3, attendanceTime);
        pstmt.setString(4, teacherId);
        pstmt.executeUpdate();
        pstmt.close();
        closeConnection();
    }



    public int getAttendanceCount(String attendance_time, String teacher_id) throws Exception{
        initConnection();
        String sql = "select count(*) from attendance where attendance_time = ? and course_id in (select id from course where teacher_id = ?)";
        int count = 0;
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, attendance_time);
        ps.setString(2, teacher_id);
        ResultSet rs = ps.executeQuery();
        if (rs.next()){
            count = rs.getInt(1);
        }
        rs.close();
        closeConnection();
        return count;
    }

    public List<Attendance> getAttendanceByTeacherId(String teacherId, String attendanceTime) throws Exception{
        initConnection();
        String sql = "select * from attendance where teacher_id = ? and attendance_time = ?";
        ArrayList<Attendance> attendances = new ArrayList<>();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, teacherId);
        pstmt.setString(2, attendanceTime);
        ResultSet rs = pstmt.executeQuery();
        getAllAttendance(attendances, rs);
        rs.close();
        pstmt.close();
        closeConnection();
        return attendances;
    }

    public void deleteAttendance(String attendance_time, String teacher_id) throws Exception{
        initConnection();
        String sql = "delete from attendance where attendance_time = ? and course_id in (select id from course where teacher_id = ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, attendance_time);
        pstmt.setString(2, teacher_id);
        pstmt.executeUpdate();
        pstmt.close();
        closeConnection();
    }

    public void addAttendance(String stu_id, String course_id, String attendance_time, String teacher_id, String status) throws Exception{
        initConnection();
        String sql = "insert into attendance values(?, ?, ?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, stu_id);
        pstmt.setString(2, course_id);
        pstmt.setString(3, attendance_time);
        pstmt.setString(4, teacher_id);
        pstmt.setString(5, status);
        pstmt.executeUpdate();
        pstmt.close();
        closeConnection();
    }

    public Map<String, String> getAttendanceOnePage(Map<String, String> attendanceMap, int currentIndex, int size) {
        Map<String, String> attendanceMap1 = new LinkedHashMap<>();
        int i = 0;
        for (Map.Entry<String, String> entry : attendanceMap.entrySet()) {
            if (i >= (currentIndex - 1) * size && i < currentIndex * size) {
                attendanceMap1.put(entry.getKey(), entry.getValue());
            }
            i++;
        }
        return attendanceMap1;
    }


    public List<Attendance> getAttendanceDetailOnePage(List<Attendance> attendances, int currentIndex, int size) {
        List<Attendance> attendances1 = new ArrayList<>();
        int i = 0;
        for (Attendance attendance : attendances) {
            if (i >= (currentIndex - 1) * size && i < currentIndex * size) {
                attendances1.add(attendance);
            }
            i++;
        }
        return attendances1;
    }
}
